use WebSecurity;
GO
-- Drop stored procedure if it already exists
IF EXISTS (
  SELECT * 
    FROM INFORMATION_SCHEMA.ROUTINES 
   WHERE SPECIFIC_CATALOG = N'WebSecurity' 
	 AND SPECIFIC_SCHEMA = N'dbo'
     AND SPECIFIC_NAME = N'Application_Search' 
)
   DROP PROCEDURE dbo.Application_Search
GO
create procedure Application_Search
    @PageSize int ,
    @PageNumber int, 
    @SortColumn nVarChar (100) ,
    @SortType nVarChar (4),
    @TotalRows int = null OUTPUT, 
@ApplicationName NVarChar(100) = N'%' , 
	@RowsAffected int = null OUTPUT 
--*************************************************************
--Created By: trooks
--Name: .dbo_Application_Search
--Table: Application
--Database: WebSecurity
--Created Date: 01/21/2013 00:00:00
--*************************************************************

as
	Begin Try
        Declare @StartIndex int = (@PageNumber - 1) * @PageSize + 1
        Declare @EndingIndex int = (@PageSize - 1) * @PageNumber + @PageNumber 
        Set @TotalRows = (Select Count(*) from dbo.Application)
        IF @SortType = 'asc'
				BEGIN
					WITH tempApplication  AS
					(
                        SELECT	ROW_NUMBER() OVER (ORDER BY @SortColumn  asc ) AS RowNumber,
		ApplicationID, 
		ApplicationName, 
		TS, 
		CreatedBy, 
		CreatedDt, 
		ModifiedBy, 
		ModifiedDt 
        
		From [dbo].Application 
Where ApplicationName Like '%' + @ApplicationName + '%'  
		)
                    SELECT	RowNumber, 
		ApplicationID, 
		ApplicationName, 
		TS, 
		CreatedBy, 
		CreatedDt, 
		ModifiedBy, 
		ModifiedDt 
                    FROM	tempApplication
					WHERE	RowNumber BETWEEN @StartIndex AND @EndingIndex
					ORDER BY RowNumber ASC;
                    
		        END
			ELSE
                BEGIN
					WITH tempApplication  AS
					(
                        SELECT	ROW_NUMBER() OVER (ORDER BY @SortColumn  desc ) AS RowNumber,
		ApplicationID, 
		ApplicationName, 
		TS, 
		CreatedBy, 
		CreatedDt, 
		ModifiedBy, 
		ModifiedDt 
        
		From [dbo].Application 
Where ApplicationName Like '%' + @ApplicationName + '%'  
		)
                    SELECT	RowNumber, 
		ApplicationID, 
		ApplicationName, 
		TS, 
		CreatedBy, 
		CreatedDt, 
		ModifiedBy, 
		ModifiedDt 
                    FROM	tempApplication
					WHERE	RowNumber BETWEEN @StartIndex AND @EndingIndex
					ORDER BY RowNumber ASC;
                    
		        END
		Set @RowsAffected = @@ROWCOUNT;
		return 0;	
	END TRY
	BEGIN CATCH
		Set @RowsAffected = 0;
		EXEC dbo.uspLogError;
		return -1;
	END CATCH
	
GO
--GRANT EXECUTE ON [WebSecurity].[dbo].[Application_Search] to PORTAL_WebSecurity